package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.StoreGoodsOrder;
import com.xmy.cultivate.entity.StoreGoodsOrderBatch;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 加订表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-08-01
 */
public interface StoreGoodsOrderBatchMapper extends BaseMapper<StoreGoodsOrderBatch> {


    @Select("select t1.*,getSchoolName(t1.school_id) as school_name,getCourseTypeName(t1.class_type_id) as class_type_name,getDictName(t1.year_class_id, 14) as year_class_name,(select IFNULL(MAX(lesson_num),0) from store_goods_order where batch_num = t1.id) as max_lesson_num,t2.created_at as affirm_time  from store_goods_order_batch as t1 LEFT JOIN store_goods_affirm as t2 on t1.id = t2.order_id and t2.type = ${affirmType} ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "storeGoodsOrderList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.StoreGoodsOrderMapper.selectListForBatchNum")
            )
    })
    public abstract IPage<StoreGoodsOrderBatch> getListForSearch(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper,@Param("affirmType") Integer affirmType);

    @Select({"<script>",
            "SELECT",
                      "* ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.id,",
                      "  t1.year_part,",
                        "t1.quarter_num,",
                        "t1.batch_num,",
                        "getDictName ( t4.year_class_id, 14 ) AS year_class_name,",
                        "getCourseTypeName ( t4.class_type_id ) AS class_type_name,",
                        "getSubjectsName ( t4.subjects_id ) AS subjects_name,",
                        "t4.class_type_id,",
                        "t4.year_class_id,",
                        "t4.lesson_type,",
                        "( SELECT MAX( created_at ) AS affirm_time FROM store_goods_affirm AS t01 WHERE t01.order_id = t1.id AND type = ${affirmType} ) AS affirm_time ",
                      "FROM",
                        "store_goods_batch AS t1",
                        "INNER JOIN store_goods_batch_detail AS t2 ON t1.id = t2.goods_batch_id",
                        "INNER JOIN store_goods_order_batch AS t3 ON t2.goods_order_batch_id = t3.id ",
                        "AND t3.deleted = 0",
                        "INNER JOIN store_goods_order AS t4 ON t3.id = t4.batch_num ",
                        "AND t4.deleted = 0 ",
                      ") AS t ",
                    "${ew.customSqlSegment}",
                     //"GROUP BY",
                      //"batch_num,",
                      //"class_type_id,",
                      //"year_class_id",
    "</script>"})
    public abstract IPage<StoreGoodsOrderBatch> getAlreadyOrderGroup(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper,@Param("affirmType") Integer affirmType);

    @Select({"<script>",
            "SELECT",
                    "t1.id,",
                    "t1.year_part,",
                    "t1.quarter_num,",
                    "t1.batch_num,",
                    "t1.subjects_id,",
                    "t3.lesson_type,",
                    "t4.class_type_id,",
                    "t3.year_class_id,",
                    "getCourseTypeName ( t4.class_type_id ) AS class_type_name,",
                    "getDictName ( t4.year_class_id, 14 ) AS year_class_name,",
                    "getSubjectsName ( t4.subjects_id ) AS subjects_name,",
                    "t4.lesson_num,",
                    "SUM( t4.goods_count ) AS goods_count,",
                    "t5.created_at AS affirm_time ",
                    "FROM",
                    "store_goods_batch AS t1",
                    "INNER JOIN store_goods_batch_detail AS t2 ON t1.id = t2.goods_batch_id",
                    "INNER JOIN store_goods_order_batch AS t3 ON t2.goods_order_batch_id = t3.id ",
                    "AND t3.deleted = 0",
                    "INNER JOIN store_goods_order AS t4 ON t3.id = t4.batch_num ",
                    "AND t4.deleted = 0 ",
                    "LEFT JOIN store_goods_affirm as t5 on t5.order_id = t1.id and t5.type = ${affirmType}",
                    "${ew.customSqlSegment}",
            "</script>"})
    public abstract List<StoreGoodsOrder> getAlreadyOrder(@Param(Constants.WRAPPER) Wrapper wrapper,@Param("affirmType") Integer affirmType);

    @Select({"<script>",
            "SELECT",
            "IFNULL(MAX(t4.lesson_num),0) as max_lesson_num",
            "FROM",
            "store_goods_batch AS t1",
            "INNER JOIN store_goods_batch_detail AS t2 ON t1.id = t2.goods_batch_id",
            "INNER JOIN store_goods_order_batch AS t3 ON t2.goods_order_batch_id = t3.id ",
            "AND t3.deleted = 0",
            "INNER JOIN store_goods_order AS t4 ON t3.id = t4.batch_num ",
            "AND t4.deleted = 0 ",
            "LEFT JOIN store_goods_affirm as t5 on t5.order_id = t3.id and t5.type = ${affirmType}",
            "${ew.customSqlSegment}",
            "</script>"})
    public abstract Integer getMaxLessonNum(@Param(Constants.WRAPPER) Wrapper wrapper,@Param("affirmType") Integer affirmType);

}
